# -*- coding:utf-8 -*-
import os.path

import pandas as pd

from infrastructure.utils.dbUtil import SqliteUtil, SqliteConfig
from config.baseConfiguration import Sqlite3Config, ReportConfig
from infrastructure.utils.result import R

config = SqliteConfig(dbPath=Sqlite3Config.dbPath)
filename = '市场投机分析.xlsx'
filename = os.path.join(ReportConfig.reportPath, filename)


def spaceBoardView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select dti as 日期, group_concat(rc, ', ') as 代码, group_concat(r, ', ') as '名称(开, 最高, 最低, 收)'
        from (
            select b.dti
                , b.count || '.' || i.code rc
                , case when b.price is not null and m.zf_open is not null then b.count || '.' || i.name || '(' || m.zf_open || ',' || m.zf_high || ',' || m.zf_low || ',' || m.zf_close || ')' 
                    else b.count || '.' || i.name end as r
                , b.count
            from stock_board b
                left join stock_move m on m.code=b.code and m.dti=b.tdti
                left join stock_info i on b.code = i.code
            where b.type = 1 and b.count >= 3
            order by count desc
        ) group by dti
        order by dti desc
        """

    boards = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(boards) == 0:
        return
    else:
        boards = [list(g) for g in boards]

    df = pd.DataFrame(boards, columns=columns)
    sheet_name = "空间分析"
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)


def sentimentViewMain():
    spaceBoardView()


if __name__ == "__main__":
    sentimentViewMain()
